/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IUserData.
	/// </summary>
	internal sealed class UserData : IUserData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new user.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_user (user_name, user_pwd, create_date) " +
			"VALUES(@user_name, @user_pwd, @create_date)";

		/// <summary>
		/// The SQL to delete a user.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_user " +
			"WHERE user_id = @user_id";

		/// <summary>
		/// The SQL to update a user.
		/// </summary>
		private static readonly string SQL_UPDATE = 
			"UPDATE cg_security_user " +
			"SET user_name = @user_name " +
			"WHERE user_id = @user_id";

		/// <summary>
		/// The SQL to update the password for a user.
		/// </summary>
		private static readonly string SQL_UPDATE_PWD = 
			"UPDATE cg_security_user SET user_pwd = @new_user_pwd " +
			"WHERE user_id = @user_id AND user_pwd = @old_user_pwd";

		/// <summary>
		/// The SQL to find all users.
		/// </summary>
		private static readonly string SQL_FIND_ALL = 
			"SELECT user_id, user_name, user_pwd, create_date, last_login " +
			"FROM cg_security_user " +
			"ORDER BY user_name";

		/// <summary>
		/// The SQL to find a user by user name.
		/// </summary>
		private static readonly string SQL_FIND_BY_USER_NAME = 
			"SELECT user_id, user_name, user_pwd, create_date, last_login " +
			"FROM cg_security_user " +
			"WHERE user_name = @user_name";

		/// <summary>
		/// The SQL to find a user by primary key.
		/// </summary>
		private static readonly string SQL_FIND_BY_PK = 
			"SELECT user_id, user_name, user_pwd, create_date, last_login " +
			"FROM cg_security_user " +
			"WHERE user_id = @user_id";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_USER_ID = "@user_id";
		private static readonly string PARM_USER_NAME = "@user_name";
		private static readonly string PARM_USER_PWD = "@user_pwd";
		private static readonly string PARM_OLD_USER_PWD = "@old_user_pwd";
		private static readonly string PARM_NEW_USER_PWD = "@new_user_pwd";
		private static readonly string PARM_CREATE_DATE = "@create_date";
		private static readonly string PARM_TABLE_NAME = "cg_security_user";

		#endregion
		
		// ******************************************************************
		// IUserData implementation.
		// ******************************************************************

		#region IUserData implementation.

		/// <summary>
		/// Creates a new system-level user.
		/// </summary>
		/// <param name="userName">The user name (must be unique).</param>
		/// <param name="userPwd">The user password (must be hashed).</param>
		/// <returns>The identifier for the new user.</returns>
		public int Create(
			string userName,
			string userPwd
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_USER_PWD, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_CREATE_DATE, OleDbType.DBTimeStamp)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userName;
			parms[1].Value = userPwd;
			parms[2].Value = "#" + DateTime.Now + "#";

			OleDbConnection conn = null;

			try
			{

				// Create a new connection.
				conn = new OleDbConnection(
					settings.ConnectionString
					);
                
				// Open the connection.
				conn.Open();

				// Execute the SQL statement.
				OleDbHelper.ExecuteNonQuery(
					conn,
					CommandType.Text, 
					SQL_CREATE, 
					parms
					);

				// Return the identity value.
				return IdentityHelper.GetLastValue(
					conn, 
					PARM_TABLE_NAME
					);

			} // End try

			finally
			{

				// Should we close the connection?
				if (conn != null && conn.State != ConnectionState.Closed)
					conn.Close();

			} // End finally

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes a system-level user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		public void Delete(
			int userID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Updates the user name for the specified user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="userName">The new user name (must be unique).</param>
		public void Update(
			int userID,
			string userName
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_NAME, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userName;
			parms[1].Value = userID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE, 
				parms
				);

		} // End Update()

		// ******************************************************************

		/// <summary>
		/// Updates the password for the specified user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="oldUserPwd">The existing user password (must be hashed).</param>
		/// <param name="newUserPwd">The new user password (must be hashed).</param>
		public int UpdatePassword(
			int userID,
			string oldUserPwd,
			string newUserPwd
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE_PWD
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_NEW_USER_PWD, OleDbType.VarChar, 50),
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_OLD_USER_PWD, OleDbType.VarChar, 50)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE_PWD, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = newUserPwd;
			parms[1].Value = userID;
			parms[2].Value = oldUserPwd;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE_PWD, 
				parms
				);

		} // End UpdatePassword()

		// ******************************************************************

		/// <summary>
		/// Returns all the system-level users in the database.
		/// </summary>
		/// <returns>A DataSet containing all the users.</returns>
		public System.Data.DataSet FindAll()
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_ALL
				);

		} // End FindAll()


		// ******************************************************************

		/// <summary>
		/// Returns the properties for the specified user.
		/// </summary>
		/// <param name="userName">The name of the user.</param>
		/// <returns>A DataReader containing the properties for the specified user.</returns>
		public System.Data.IDataReader FindByUserName(
			string userName
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_USER_NAME
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_NAME, OleDbType.VarChar, 50)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_USER_NAME, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userName;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteReader(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_USER_NAME, 
				parms
				);

		} // End FindByUserName()
		
		// ******************************************************************

		/// <summary>
		/// Returns the properties for the specified user.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <returns>A DataReader containing the properties for the specified user.</returns>
		public System.Data.IDataReader FindByPK(
			int userID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_PK
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_PK, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteReader(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_PK, 
				parms
				);

		} // End FindByPK()

		#endregion 

	} // End class UserData

} // End namespace CG.Security.Data.Access

